"""
Case Type   : 防篡改
Case Name   : 三权分立关闭时验证将用户加入到组之后，对之后创建的数据对象有所有权限
Create At   : 2022-02-21
Owner       : lwx1080719
Description :
    1.查询三权分立参数enableSeparationOfDuty
    2.创建sysadmin和普通用户
    3.切换sysadmin用户授权给普通用户
    4.切换sysadmin用户创建数据库对象
    5.普通用户连接新数据创建schema
    6.切换普通用户进行insert,create,call操作
    7.清理环境
Expect      :
    1.显示默认值off
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
History     :
"""

import os
import unittest
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class ModifyCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.common = Common()
        self.user_name = 'u_security_role_members_0010'
        self.table_name = 't_security_role_members_0010'
        self.database_name = 'd_security_role_members_0010'
        self.tablespace_name = 'ts_security_role_members_0010'
        self.schema_name = 's_security_role_members_0010'
        self.function_name = 'f_security_role_members_0010'
        self.default_value = self.common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '----step1:查询三权分立参数enableSeparationOfDuty值  ' \
               'expect:默认值off----'
        self.logger.info(text)
        self.assertEqual("off", self.default_value, "执行失败:" + text)

        text = '----step2:创建sysadmin和普通用户 expect:创建成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            drop user if exists {self.user_name}_sys;
            drop user if exists {self.user_name}_noruser;
            create user {self.user_name}_sys sysadmin  password 
            '{macro.PASSWD_REPLACE}';
            create user {self.user_name}_noruser password 
            '{macro.PASSWD_REPLACE}';''')
        self.logger.info(sql_cmd)
        self.assertTrue(sql_cmd.count
                        (self.Constant.DROP_ROLE_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertTrue(
            sql_cmd.count(self.Constant.CREATE_ROLE_SUCCESS_MSG) == 2,
            "执行失败:" + text)

        text = '----step3:切换sysadmin用户授权给普通用户 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            set role {self.user_name}_sys password '{macro.PASSWD_REPLACE}';
            grant  {self.user_name}_sys to {self.user_name}_noruser;''')
        self.logger.info(sql_cmd)
        self.assertIn(self.Constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.GRANT_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)

        text = '----step4:切换sysadmin用户创建数据库对象 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
                            set role {self.user_name}_sys password '{macro.PASSWD_REPLACE}';
                            drop table if exists {self.user_name}_sys.{self.table_name}_1;
                            create table {self.user_name}_sys.{self.table_name}_1
                            (id int primary key,name varchar(100));
                            drop database if exists {self.database_name};
                            create database {self.database_name};
                            drop tablespace if exists {self.tablespace_name};
                            create tablespace {self.tablespace_name} relative 
                            location '{self.tablespace_name}';
                            drop schema if exists {self.schema_name}_1;
                            create schema {self.schema_name}_1;
                            drop function if exists 
                            {self.user_name}_sys.{self.function_name};
                            create function {self.user_name}_sys.{self.function_name}
                            return integer
                            as
                            begin
                            return 1;
                            end;''')
        self.logger.info(sql_cmd)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertTrue(
            sql_cmd.count(self.Constant.TABLE_CREATE_SUCCESS) == 3,
            "执行失败:" + text)
        self.assertIn(self.Constant.DROP_DATABASE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.CREATE_DATABASE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.DROP_FUNCTION_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.CREATE_FUNCTION_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.TABLESPCE_DROP_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.TABLESPCE_CREATE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.DROP_SCHEMA_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.CREATE_SCHEMA_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)

        text = '----step5:普通用户连接新数据创建schema expect:成功----'
        self.logger.info(text)
        sql_cmd = f'drop schema if exists {self.schema_name}_2;' \
                  f'create schema {self.schema_name}_2;'
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_noruser -W '
            f'{macro.PASSWD_REPLACE}', dbname=f'{self.database_name}')
        self.logger.info(msg)
        self.assertIn(self.Constant.DROP_SCHEMA_SUCCESS_MSG, msg,
                      "执行失败:" + text)
        self.assertIn(self.Constant.CREATE_SCHEMA_SUCCESS_MSG, msg,
                      "执行失败:" + text)

        text = '----step6:切换普通用户进行insert,create,call操作 ' \
               'expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            set role {self.user_name}_noruser password 
            '{macro.PASSWD_REPLACE}';
            insert into {self.user_name}_sys.{self.table_name}_1 
            values(1,'beijing'),(2,'xian');
            drop table if exists 
            {self.user_name}_noruser.{self.table_name}_2;
            create table {self.user_name}_noruser.{self.table_name}_2
            (id int primary key,name varchar(100))
            tablespace {self.tablespace_name};
            call  {self.user_name}_sys.{self.function_name}();''')
        self.logger.info(sql_cmd)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertTrue(
            sql_cmd.count(self.Constant.TABLE_CREATE_SUCCESS) == 2,
            "执行失败:" + text)
        self.assertEqual("1", sql_cmd.split("\n")[-2].strip(),
                         "执行失败:" + text)

    def tearDown(self):
        text = '----step7:清理环境 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
                    drop table {self.user_name}_sys.{self.table_name}_1;
                    drop table {self.user_name}_noruser.{self.table_name}_2;
                    drop tablespace {self.tablespace_name};
                    drop database {self.database_name};
                    drop schema {self.schema_name}_1;
                    drop user {self.user_name}_sys cascade;
                    drop user {self.user_name}_noruser cascade;''')
        self.logger.info(sql_cmd)
        self.assertTrue(
            sql_cmd.count(self.Constant.DROP_ROLE_SUCCESS_MSG) == 2,
            "执行失败:" + text)
        self.assertIn(self.Constant.TABLESPCE_DROP_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertTrue(
            sql_cmd.count(self.Constant.TABLE_DROP_SUCCESS) == 3,
            "执行失败:" + text)
        self.assertIn(self.Constant.DROP_SCHEMA_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.DROP_DATABASE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
